package com.sxyjhh.dbutil.basic;

import com.sxyjhh.dbutil.meta.ColumnMetaData;
import com.sxyjhh.dbutil.meta.DatabaseAnalyzer;
import com.sxyjhh.dbutil.meta.PrimaryKeyMetaData;
import com.sxyjhh.dbutil.meta.TableMetaData;
import com.sxyjhh.dbutil.service.QueryRecordService;
import com.sxyjhh.dbutil.utils.CommonUtils;
import com.sxyjhh.dbutil.utils.PageInfo;
import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.io.Serializable;
import java.sql.*;
import java.util.*;

/**
 * Description:数据库查询封装类
 *
 * @JDK Version:1.8.0_40
 * Version: V1.0
 * Author: wangchun
 * Date: 2016/3/29 11:15
 */
@Log4j
public class DBQueryUtils implements Serializable{

    private CustomDataSource dataSource;

    public interface DBName {
        String MYSQL = "mysql";
        String ORACLE = "oracle";
        String SQL_SERVER = "sqlserver";
        String DM = "dm.jdbc";
    }

    public DBQueryUtils(String driverName, String dbUrl, String userName, String password, int connPoolSize) {
        dataSource = DBCPManager.getInstance().getCustomDataSource(driverName, dbUrl, userName, password, connPoolSize);
    }

    /**
     * @param tableName 表名
     * @return 返回得到的数据列表
     * @throws SQLException
     */
    public List queryTableData(String tableName) throws SQLException {
        QueryRunner runner = new QueryRunner(dataSource.getBds());
        String sql = "select * from " + tableName;
        return (List) runner.query(sql, new MapListHandler());
    }

    /**
     * 按分页的方式查询表的数据
     *
     * @param dbName
     * @param tableName   表名称
     * @param currentPage 当前页
     * @param numPerPage  每页条数
     * @return 返回页面信息，以及数据结果
     * @throws SQLException
     */
    public PageInfo queryTableDataByPaging(String dbName, String tableName, long currentPage, int numPerPage, String sql) throws Exception {
        String driverClassName = dataSource.getBds().getDriverClassName();
        ConstConfig.DBType dbType = getDbType(driverClassName);

        //判断如果是SQLServer数据库则需要查出该表的中的 主键，或者有唯一索引的字段，以便分页中使用
        String pkColumnName = null;
        if (dbType == ConstConfig.DBType.SQLSERVER) {
            List<PrimaryKeyMetaData> primaryKeyList = getPrimaryKeyList(dbName, tableName);
            if (primaryKeyList != null && primaryKeyList.size() > 0) {
                PrimaryKeyMetaData primaryKeyMetaData = primaryKeyList.get(0);
                pkColumnName = primaryKeyMetaData.getName();
            } else {
                List<ColumnMetaData> columns = getColumns(dbName, tableName);
                if (columns != null && columns.size() > 0) {
                    pkColumnName = columns.get(0).getName();
                }
            }
        }

        QueryRunner queryRunner = new QueryRunner(dataSource.getBds());
        return new Pagination(dbType, sql, currentPage, numPerPage, new MapListHandler(), queryRunner, tableName).queryData(pkColumnName);
    }

    /**
     * 按分页的方式查询表的数据
     *
     * @param dbName
     * @param tableName   表名称
     * @param currentPage 当前页
     * @param numPerPage  每页条数
     * @param totalRows   总记录数
     * @return 返回页面信息，以及数据结果
     * @throws SQLException
     */
    public PageInfo queryTableDataByPaging(String dbName, String tableName, long currentPage, int numPerPage, String sql, long totalRows) throws Exception {
        String driverClassName = dataSource.getBds().getDriverClassName();
        ConstConfig.DBType dbType = getDbType(driverClassName);

        //判断如果是SQLServer数据库则需要查出该表的中的 主键，或者有唯一索引的字段，以便分页中使用
        String pkColumnName = null;
        if (dbType == ConstConfig.DBType.SQLSERVER) {
            List<PrimaryKeyMetaData> primaryKeyList = getPrimaryKeyList(dbName, tableName);
            if (primaryKeyList != null && primaryKeyList.size() > 0) {
                PrimaryKeyMetaData primaryKeyMetaData = primaryKeyList.get(0);
                pkColumnName = primaryKeyMetaData.getName();
            } else {
                List<ColumnMetaData> columns = getColumns(dbName, tableName);
                if (columns != null && columns.size() > 0) {
                    pkColumnName = columns.get(0).getName();
                }
            }
        }

        QueryRunner queryRunner = new QueryRunner(dataSource.getBds());
        return new Pagination(dbType, sql, currentPage, numPerPage, new MapListHandler(), queryRunner, tableName, totalRows).queryData(pkColumnName);
    }

    public PageInfo queryPageinfo(String tableName, long currentPage, int numPerPage, String sql) throws Exception {
        String driverClassName = dataSource.getBds().getDriverClassName();
        ConstConfig.DBType dbType = getDbType(driverClassName);
        ;
        QueryRunner queryRunner = new QueryRunner(dataSource.getBds());
        return new Pagination(dbType, sql, currentPage, numPerPage, new MapListHandler(), queryRunner, tableName).queryPageinfo();
    }

    public Object queryFieldMaxValue(String sql) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(dataSource.getBds());
        Object result = queryRunner.query(sql, new ScalarHandler());
        return result;
    }

    public List<PrimaryKeyMetaData> getPrimaryKeyList(String dbName, String tableName) throws Exception {
        Connection connection = dataSource.getBds().getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        List<PrimaryKeyMetaData> primaryKeyMetaDatas = null;
        try {
            primaryKeyMetaDatas = DatabaseAnalyzer.getPrimaryKeys(metaData, dbName, tableName);
        } finally {
            connection.close();
        }
        return primaryKeyMetaDatas;
    }

    public List<PrimaryKeyMetaData> getPrimaryKeyList(String dbName, String tableName, String otherUser) throws Exception {
        Connection connection = dataSource.getBds().getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        List<PrimaryKeyMetaData> primaryKeyMetaDatas = null;
        try {
            primaryKeyMetaDatas = DatabaseAnalyzer.getPrimaryKeys(metaData, dbName, tableName);
        } finally {
            connection.close();
        }
        return primaryKeyMetaDatas;
    }

    /**
     * @param dbName 数据库名
     * @return 返回数据库表元数据
     * @throws SQLException
     */
    public List<TableMetaData> getTableMetaDataByDBName(String dbName) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        List<TableMetaData> tablesSimple = null;
        try {
            tablesSimple = DatabaseAnalyzer.getTablesSimple(connection, dbName);
        } finally {
            connection.close();
        }
        return tablesSimple;
    }

    public List<TableMetaData> getTableMetaDataByDBName(String dbName, String otherUser) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        List<TableMetaData> tablesSimple = null;
        try {
            tablesSimple = DatabaseAnalyzer.getTablesSimple(connection, dbName, otherUser);
        } finally {
            connection.close();
        }
        return tablesSimple;
    }

    public List<ColumnMetaData> getColumns(String dbName, String tableName, List<PrimaryKeyMetaData> primaryKeyList) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        List<ColumnMetaData> columnMetaData = null;
        try {
            String driverClassName = dataSource.getBds().getDriverClassName();
            ConstConfig.DBType dbType = getDbType(driverClassName);
            DatabaseMetaData metaData = connection.getMetaData();
            columnMetaData = DatabaseAnalyzer.getColumns(metaData, dbName, tableName, dbType);
        } finally {
            connection.close();
        }
        return columnMetaData;
    }

    public List<ColumnMetaData> getColumns(String dbName, String tableName, List<PrimaryKeyMetaData> primaryKeyList, String otherUser) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        List<ColumnMetaData> columnMetaData = null;
        try {
            String driverClassName = dataSource.getBds().getDriverClassName();
            ConstConfig.DBType dbType = getDbType(driverClassName);
            DatabaseMetaData metaData = connection.getMetaData();
            columnMetaData = DatabaseAnalyzer.getColumns(metaData, dbName, tableName, dbType, otherUser);
        } finally {
            connection.close();
        }
        return columnMetaData;
    }

    /**
     * 获取某一个表所有的字段
     *
     * @param dbName    数据库名
     * @param tableName 表名
     * @return
     * @throws SQLException
     */
    public List<ColumnMetaData> getColumns(String dbName, String tableName) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        List<ColumnMetaData> columnMetaData = null;
        try {
            String driverClassName = dataSource.getBds().getDriverClassName();
            ConstConfig.DBType dbType = getDbType(driverClassName);
            DatabaseMetaData metaData = connection.getMetaData();
            columnMetaData = DatabaseAnalyzer.getColumns(metaData, dbName, tableName, dbType);
        } finally {
            connection.close();
        }
        return columnMetaData;
    }

    public List<ColumnMetaData> getColumns(String dbName, String tableName, String otherUser) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        List<ColumnMetaData> columnMetaData = null;
        try {
            String driverClassName = dataSource.getBds().getDriverClassName();
            ConstConfig.DBType dbType = getDbType(driverClassName);
            DatabaseMetaData metaData = connection.getMetaData();
            columnMetaData = DatabaseAnalyzer.getColumns(metaData, dbName, tableName, dbType, otherUser);
        } finally {
            connection.close();
        }
        return columnMetaData;
    }

    public void closeDataSource() throws SQLException {
        dataSource.closeDataSource();
    }

    private ConstConfig.DBType getDbType(String driverClassName) {
        ConstConfig.DBType dbType = null;
        if (driverClassName.toLowerCase().contains(DBName.MYSQL)) {
            dbType = ConstConfig.DBType.MYSQL;
        } else if (driverClassName.toLowerCase().contains(DBName.ORACLE)) {
            dbType = ConstConfig.DBType.ORACLE;
        } else if (driverClassName.toLowerCase().contains(DBName.SQL_SERVER)) {
            dbType = ConstConfig.DBType.SQLSERVER;
        } else if (driverClassName.toLowerCase().contains(DBName.DM)) {
            dbType = ConstConfig.DBType.DAMENG;
        }
        return dbType;
    }

    /**
     * 只适用于oracle or dameng
     *
     * @param tableName
     * @return
     */
    public boolean isExistTable(String tableName, ConstConfig.DBType dbType) throws SQLException {
        String sql = "";
        switch (dbType) {
            case MYSQL:
                sql = "select count(1) from information_schema.tables where  table_name = '" + tableName + "'";
                break;
            case DAMENG:
                sql = "select count(1) from user_tables where TABLE_NAME = '" + tableName + "'";
                break;
            case ORACLE:
                sql = "select count(1) from user_tables where TABLE_NAME = '" + tableName + "'";
                break;
            case SQLSERVER:
                sql = "select count(1) from sys.tables where name='" + tableName + "'";
                break;
            default:
                return false;

        }

        Statement statement = null;
        Connection connection = null;
        try {
            connection = dataSource.getBds().getConnection();
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {  //通过next来索引：判断是否有下一个记录
                long count = resultSet.getLong(1);
                return count > 0;
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return false;
    }

    public boolean execDDLSql(String... ddlSqls) throws SQLException {
        Statement statement = null;
        Connection connection = null;
        try {
            connection = dataSource.getBds().getConnection();
            statement = connection.createStatement();
            for (String ddlSql : ddlSqls) {
                statement.executeUpdate(ddlSql);
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }

        return true;
    }

    @Deprecated
    public boolean insertData(String tableName, String dbName, Map<String, String> dataMap) throws SQLException {
        if (!StringUtils.isNotEmpty(tableName) || dataMap == null || dataMap.size() <= 0) {
            return false;
        }

        boolean result = true;
        try {
            List<String> columnNameList = new ArrayList<String>();
            StringBuffer columnValueBuf = new StringBuffer();

            List<ColumnMetaData> columns = getColumns(dbName, tableName);
            for (ColumnMetaData columnMetaData : columns) {
                String columnName = columnMetaData.getName();
                String columnType = columnMetaData.getType();
                if (dataMap.containsKey(columnName)) {
                    String columnValue = dataMap.get(columnName);
                    if (StringUtils.equals("TIMESTAMP", columnType) && !StringUtils.equals("DATETIME", columnType)) {
                        if (columnValue != null && StringUtils.isNotEmpty(columnValue) && !StringUtils.equalsIgnoreCase("NULL", columnValue)) {
                            Timestamp date = new Timestamp(Long.parseLong(columnValue));
                            columnValue = CommonUtils.getStrDate(date, CommonUtils.YYYYMMDDHHMMSS);
                        } else {
                            columnValue = null;
                        }
                    } else if (StringUtils.equals("DATE", columnType)) {
                        if (columnValue != null && StringUtils.isNotEmpty(columnValue) && !StringUtils.equalsIgnoreCase("NULL", columnValue)) {
                            Timestamp date = new Timestamp(Long.parseLong(columnValue));
                            columnValue = CommonUtils.getStrDate(date, CommonUtils.YYYYMMDD);
                        } else {
                            columnValue = null;
                        }
                    }
                    if (!StringUtils.isNotEmpty(columnValue) || StringUtils.equalsIgnoreCase("NULL", columnValue)) {
                        columnValue = null;
                    } else if (columnValue != null && !StringUtils.isNumeric(columnValue)) {
                        columnValue = "'" + columnValue + "'";
                    }
                    columnNameList.add(columnName);
                    columnValueBuf.append(columnValue);
                    columnValueBuf.append(",");
                }
            }

            String driverClassName = dataSource.getBds().getDriverClassName();
            ConstConfig.DBType dbType = getDbType(driverClassName);

            //拼装insert语句
            StringBuffer strBuf = new StringBuffer();
            strBuf.append("INSERT INTO ");

            switch (dbType) {
                case DAMENG:
                    strBuf.append("\"" + dbName.toUpperCase() + "\".\"" + tableName + "\"");
                    strBuf.append(" (\"");
                    strBuf.append(StringUtils.join(columnNameList, "\",\""));
                    strBuf.append("\") ");
                    break;
                default:
                    strBuf.append(tableName);
                    strBuf.append(" (");
                    strBuf.append(StringUtils.join(columnNameList, ","));
                    strBuf.append(") ");
                    break;
            }

            strBuf.append(" VALUES ");
            strBuf.append(" (");
            strBuf.append(columnValueBuf.substring(0, columnValueBuf.lastIndexOf(",")));
            strBuf.append(") ");


            QueryRunner queryRunner = new QueryRunner(dataSource.getBds());
            queryRunner.insert(strBuf.toString(), new ArrayHandler());
        } catch (Exception e) {
            result = false;
            throw e;
        }

        return result;
    }

    public boolean insertBatchData(String tableName, String dbName, List<Map<String, String>> dataList) throws SQLException {
        if (!StringUtils.isNotEmpty(tableName) || dataList == null || dataList.size() <= 0) {
            return false;
        }
        boolean result = true;
        try {
            List<Object[]> paramsList = new ArrayList();
            //获取title集合
            Map<String, String> firstDataMap = dataList.get(0);

            String[] columnArray = firstDataMap.keySet().toArray(new String[0]);

            //过滤column name为空的情况
            List<String> columnList = new ArrayList<>();
            for (String columnStr : columnArray) {
                if (StringUtils.isNotEmpty(columnStr)) {
                    columnList.add(columnStr);
                }
            }

            columnArray = columnList.toArray(new String[0]);


            String driverClassName = dataSource.getBds().getDriverClassName();
            ConstConfig.DBType dbType = getDbType(driverClassName);
            if (dbType == ConstConfig.DBType.ORACLE || dbType == ConstConfig.DBType.DAMENG) {
                List<ColumnMetaData> columns = getColumns(dbName, tableName);
                for (int i = 0; i < dataList.size(); i++) {
                    Map<String, String> dataMap = dataList.get(i);
                    //为了解决set是无序的集合，避免数据错乱，需要循环取值
                    List<Object> data = new ArrayList<>();
                    for (String columnName : columnArray) {
                        if (dataMap.containsKey(columnName)) {
                            String columnValue = dataMap.get(columnName);
                            Object valObj = null;
                            String cloumnType = getColumnType(columns, columnName);
                            if (StringUtils.isNotEmpty(columnValue) && !StringUtils.equalsIgnoreCase(columnValue, "null")) {
                                valObj = columnValue;
                                if (StringUtils.equalsIgnoreCase(cloumnType, "DATE")) {
                                    String value = columnValue.trim();
                                    int indexOf = value.indexOf(" ");
                                    if (indexOf > -1) {
                                        value = value.substring(0, indexOf);
                                    }
                                    valObj = java.sql.Date.valueOf(value);
                                } else if (StringUtils.startsWithIgnoreCase(cloumnType, "DATETIME")) {
//                                    if (StringUtils.endsWith(columnValue, ".0")) {
//                                        columnValue = StringUtils.substring(columnValue, 0, StringUtils.lastIndexOf(columnValue, ".0"));
//                                    }
                                    valObj = java.sql.Timestamp.valueOf(columnValue);
                                } else if (StringUtils.startsWithIgnoreCase(cloumnType, "TIMESTAMP")) {
                                    valObj = java.sql.Timestamp.valueOf(columnValue);
                                }
                            }

                            if (StringUtils.startsWithIgnoreCase(cloumnType, "CHAR")
                                    || StringUtils.startsWithIgnoreCase(cloumnType, "VARCHAR")) {
                                if (valObj == null || StringUtils.equalsIgnoreCase((String) valObj, "null")
                                        || StringUtils.isEmpty(StringUtils.trim((String) valObj))) {
                                    valObj = null;
                                }
                            }
                            data.add(valObj);
                        }
                    }
                    paramsList.add(data.toArray());
                }
            } else {
                for (int i = 0; i < dataList.size(); i++) {
                    Map<String, String> dataMap = dataList.get(i);
                    //为了解决set是无序的集合，避免数据错乱，需要循环取值
                    List<Object> data = new ArrayList<>();
                    for (String columnName : columnArray) {
                        if (dataMap.containsKey(columnName)) {
                            String columnValue = dataMap.get(columnName);
                            data.add(StringUtils.isEmpty(columnValue) ? null : columnValue);
                        }
                    }
                    paramsList.add(data.toArray());
                }
            }


            //组装问号匹配符，
            List<String> flagList = new ArrayList<>();
            for (int i = 0; i < columnArray.length; i++) {
                flagList.add("?");
            }

            //拼装insert语句
            StringBuffer strBuf = new StringBuffer();
            strBuf.append("INSERT INTO ");

            switch (dbType) {
                case DAMENG:
                    strBuf.append("\"" + dbName.toUpperCase() + "\".\"" + tableName + "\"");
                    strBuf.append(" (\"");
                    strBuf.append(StringUtils.join(columnArray, "\",\""));
                    strBuf.append("\") ");
                    break;
                case ORACLE:
                    strBuf.append(tableName);
                    strBuf.append(" (\"");
                    strBuf.append(StringUtils.join(columnArray, "\",\""));
                    strBuf.append("\") ");
                    break;
                default:
                    strBuf.append(tableName);
                    strBuf.append(" (");
                    strBuf.append(StringUtils.join(columnArray, ","));
                    strBuf.append(") ");
                    break;
            }

            strBuf.append(" VALUES ");
            strBuf.append(" (");
            strBuf.append(StringUtils.join(flagList, ","));
            strBuf.append(") ");

            long startTimeSave = System.currentTimeMillis();   //获取开始时间
            QueryRunner queryRunner = new QueryRunner(dataSource.getBds());
            queryRunner.batch(strBuf.toString(), paramsList.toArray(new Object[0][0]));
            long endTimeSave = System.currentTimeMillis();    //获取结束时间
            log.info("存储目标数据运行时长：" + (endTimeSave - startTimeSave) + "ms");
        } catch (Exception e) {
            result = false;
            throw e;
        }

        return result;
    }

    /**
     * 查找列名信息集合中的指定列名的字段类型
     *
     * @param columns
     * @param columnName
     * @return
     */
    private String getColumnType(List<ColumnMetaData> columns, String columnName) {
        String cloumnType = null;
        for (ColumnMetaData columnMetaData : columns) {
            if (StringUtils.equalsIgnoreCase(columnMetaData.getName(), columnName)) {
                cloumnType = columnMetaData.getType();
            }
        }
        return cloumnType;
    }

    /**
     * 删除表数据，根据条件
     *
     * @param tableName
     * @param condMap
     * @return
     */
    public boolean delData(String tableName, Map<String, String> condMap) throws SQLException {
        StringBuffer strBuf = new StringBuffer();
        strBuf.append("DELETE FROM ");
        strBuf.append(tableName);

        if (condMap != null && condMap.size() > 0) {
            strBuf.append(" WHERE 1=1 ");
            Set<Map.Entry<String, String>> entries = condMap.entrySet();
            Iterator<Map.Entry<String, String>> iterator = entries.iterator();
            while (iterator.hasNext()) {
                Map.Entry<String, String> entry = iterator.next();
                strBuf.append(" AND ");
                strBuf.append(entry.getKey());
                strBuf.append("=");
                strBuf.append("'");
                strBuf.append(entry.getValue());
                strBuf.append("'");
            }
        }

        boolean result = true;
        try {
            QueryRunner queryRunner = new QueryRunner(dataSource.getBds());
            queryRunner.update(strBuf.toString());
        } catch (SQLException e) {
            result = false;
            throw e;
        }
        return result;
    }

    /**
     * @param querySql 查询语句
     * @return 返回得到的数据列表
     * @throws SQLException
     */
    public List queryData(String querySql) throws SQLException {
        QueryRunner runner = new QueryRunner(dataSource.getBds());
        return (List) runner.query(querySql, new MapListHandler());
    }


    public List<TableMetaData> getTablesSimple(String dbName) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        List<TableMetaData> tableMetaData = null;
        try {
            tableMetaData = DatabaseAnalyzer.getTablesSimple(connection, dbName);
        } finally {
            connection.close();
        }
        return tableMetaData;
    }

    public TableMetaData getTablesSimpleByTName(String dbName, String tableName) throws SQLException {
        Connection connection = dataSource.getBds().getConnection();
        TableMetaData tableMetaData = null;
        try {
            tableMetaData = DatabaseAnalyzer.getTablesSimpleByTName(connection, dbName, tableName);
        } finally {
            connection.close();
        }
        return tableMetaData;
    }

    public boolean getConnectStatus() {
        Connection connection = null;
        try {
            connection = dataSource.getBds().getConnection();
        } catch (SQLException e) {
            log.error("getConnectStatus", e);
        } finally {
            if (connection == null) {
                try {
                    DBCPManager.getInstance().removeDataSource(dataSource.getBds().getUrl(), dataSource.getBds().getUsername(), dataSource.getBds().getPassword());
                } catch (SQLException e) {
                    log.error("remove connect", e);
                }
            }
        }
        return connection != null;
//        return true;
    }

}
